let mysql = require('mysql');
let {db} = require('./../config')
module.exports = class Sql {
    pool = {};

    constructor() {
        this.clean();
        this.pool = mysql.createPool(db);
        console.log('已创建数据库连接池。');
    }

    //查询
    select(fieldIds = {}) {
        this.sql.push('SELECT ')
        this.level++;
        this.type.push('select')
        Array.isArray(fieldIds) ? this.fieldStr.push(fieldIds.length > 0 ? fieldIds.map(fieldId => {
            if (typeof fieldId === 'string') {
                return `${fieldId}`
            } else {
                return `${fieldId.prefix || ''} ${fieldId.runFunc ? fieldId.runFunc + '(' : ''}${fieldId.field}${fieldId.runFunc ? ')' : ''}${fieldId.rename ? ' as ' + fieldId.rename : ''}`
            }
        }).join(',') : `*`) : this.fieldStr.push(Object.keys(fieldIds).map(tableName => {
            return Array.isArray(fieldIds[tableName]) && fieldIds[tableName].length > 0 ? fieldIds[tableName].map(fieldId => {
                if (typeof fieldId === 'string') {
                    return `${tableName}.${fieldId}`
                } else {
                    return `${fieldId.prefix || ''} ${fieldId.runFunc ? fieldId.runFunc + '(' : ''}${tableName}.${fieldId.field}${fieldId.runFunc ? ')' : ''}${fieldId.rename ? ' as ' + fieldId.rename : ''}`
                }
            }).join(',') : `${tableName}.*`
        }).join(','));
        return this;
    }

    //插入的数据
    insert(data = {}) {
        if (JSON.stringify(data) === '{}') throw('need atLeast data params')
        this.level++;
        this.type.push('add');
        let fieldIds = [], values = [];
        Object.keys(data).forEach(key => {
            fieldIds.push(key);
            values.push(`${data[key]}`)
        })
        this.insertStr.push(`(${fieldIds.join(' , ')}) VALUES(${values.map(value => '?').join(' , ')})`);
        this.sqlParams = this.sqlParams.concat(...values)
        return this;
    }

    //插入哪张表
    into(tableName = '') {
        if (tableName === '') throw('need tableName')
        this.sql.push(`INSERT INTO ${tableName}`)
        return this;
    }

    //更新哪张表
    update(tableName = '') {
        if (tableName === '') throw('need tableName')
        this.type.push('update');
        this.level++;
        this.sql.push(`UPDATE ${tableName}`)
        return this;
    }

    //更新的数据
    set(kvMap = {}) {
        if (JSON.stringify(kvMap) === '{}') throw('need atLeast data params')
        let params = [];
        this.updateStr.push(' SET ' + Object.keys(kvMap).map(key => {
            params.push(kvMap[key]);
            return `${key} = ?`;
        }).join(' , '));
        this.saveParams(params)
        return this;
    }

    //删除数据
    delete() {
        this.type.push('delete');
        this.sql.push('DELETE ');
        this.level++;
        return this;
    }

    //指定操作的表
    from(tables = []) {
        if (tables.length <= 0) throw({success: false, msg: '"from" params error:tables error'});
        this.tableStr.push(typeof tables === 'string' ? ` FROM ${tables}` : ' FROM ' + tables.join(','))
        return this;
    }

    //内部函数：移除私有字段
    removePrivate(option = {}, keys = []) {
        let result = {...option};
        keys.forEach(key => delete result[key])
        return result;
    }

    //内部函数：生成运算符
    genCompare(option = {}, table, param) {
        if (option._gt) return this._gt(this.removePrivate(option, ['_gt']), table, param);
        if (option._lt) return this._lt(this.removePrivate(option, ['_lt']), table, param);
        if (option._gte) return this._gte(this.removePrivate(option, ['_gte']), table, param);
        if (option._lte) return this._lte(this.removePrivate(option, ['_lte']), table, param)
        if (option._in) return this._in(this.removePrivate(option, ['_in']), table, param);
        if (option._nIn) return this._nIn(this.removePrivate(option, ['_nIn']), table, param);
        if (option._like) return this._like(this.removePrivate(option, ['_like']), table, param);
        if (option._nEqual) return this._nEqual(this.removePrivate(option, ['_nEqual']), table, param);
        return this._equal(this.removePrivate(option), table, param)
    }

    //内部函数：！=
    _nEqual(option = {}, table = false, param) {
        let params = [];
        let result = Object.keys(option).map(key => {
            params.push(option[key]);
            return table === false ? `${param} != ?` : `${table}.${param} != ?`;
        }).join(',')
        this.saveParams(params)
        return result;
    }

    //内部函数：=
    _equal(option = {}, table = false, param) {
        let params = [];
        let result = Object.keys(option).map(key => {
            params.push(option[key]);
            return table === false ? `${param} = ?` : `${table}.${param} = ?`;
        }).join(',')
        this.saveParams(params)
        return result;
    }

    //内部函数：>
    _gt(option = {}, table = false, param) {
        let params = [];
        let result = Object.keys(option).map(key => {
            params.push(option[key]);
            return table === false ? `${param} > ?` : `${table}.${param} > ?`;
        }).join(',')
        this.saveParams(params)
        return result;
    }

    //内部函数：<
    _lt(option = {}, table = false, param) {
        let params = [];
        let result = Object.keys(option).map(key => {
            params.push(option[key]);
            return table === false ? `${param} < ?` : `${table}.${param} < ?`;
        }).join(',')
        this.saveParams(params)
        return result;
    }

    //内部函数：>=
    _gte(option = {}, table = false, param) {
        let params = [];
        let result = Object.keys(option).map(key => {
            params.push(option[key]);
            return table === false ? `${param} >= ?` : `${table}.${param} >= ?`;
        }).join(',')
        this.saveParams(params)
        return result;
    }

    //内部函数：<=
    _lte(option = {}, table = false, param) {
        let params = [];
        let result = Object.keys(option).map(key => {
            params.push(option[key]);
            return table === false ? `${param} <= ?` : `${table}.${param} <= ?`;
        }).join(',')
        this.saveParams(params)
        return result;
    }

    //内部函数：in
    _in(option = {}, table = false, param) {
        let params = []
        let result = Object.keys(option).map(key => table === false ? `${param} IN ( ${Array.isArray(option[key]) ? option[key].map(field => {
            params.push(field);
            return '?'
        }).join(',') : option[key]})` : `${table}.${param} IN ( ${Array.isArray(option[key]) ? option[key].map(field => {
            params.push(field);
            return '?'
        }).join(',') : option[key]})`).join(',');
        this.saveParams(params)
        return result;
    }

    //内部函数：like
    _like(option = {}, table = false, param) {
        let params = [];
        let result = Object.keys(option).map(key => {
            params.push(option[key]);
            return table === false ? `${param} regexp ?` : `${table}.${param} regexp ?`;
        }).join(',')
        this.saveParams(params)
        return result;
    }

    saveParams(params) {
        let level = this.level;
        if (Array.isArray(this.sqlParams[level])) {
            this.sqlParams[level] = this.sqlParams[level].concat(...params)
        } else {
            Array.isArray(this.sqlParams[level - 1]) ? this.paramsOprator.push(this.sqlParams[level - 1].length || false) : this.paramsOprator.push(false)
            this.sqlParams.push([].concat(...params))
        }
    }

    //内部函数：not in
    _nIn(option = {}, table = false, param) {
        let params = []
        let result = Object.keys(option).map(key => table === false ? `${param} NOT IN ( ${Array.isArray(option[key]) ? option[key].map(field => {
            params.push(field);
            return '?'
        }).join(',') : option[key]})` : `${table}.${param} NOT IN ( ${Array.isArray(option[key]) ? option[key].map(field => {
            params.push(field);
            return '?'
        }).join(',') : option[key]})`).join(',');
        this.saveParams(params)
        return result;
    }

    //查询条件
    where(conditions = {}) {
        if (JSON.stringify(conditions) === '{}') return this;
        Array.isArray(conditions) ? this.whereStr.push(' WHERE ' + conditions.map(condition => {
            let type = condition._type || ' AND ', nextType = condition._nextType || ' AND ';
            return Object.keys(this.removePrivate(condition, ['_type', '_nextType'])).map(option => this.genCompare(condition[option], false, option)).join(type) + nextType;
        }).join('').slice(0, -5)) : this.whereStr.push(' WHERE ' + Object.keys(conditions).map(table => {
            return conditions[table].map(condition => {
                let type = condition._type || ' AND ', nextType = condition._nextType || ' AND ';
                return Object.keys(this.removePrivate(condition, ['_type', '_nextType'])).map(option => this.genCompare(condition[option], table, option)).join(type) + nextType;
            }).join('').slice(0, -5)
        }))
        return this;
    }

    //排序
    orderBy(fieldIds) {
        if (JSON.stringify(fieldIds) === '{}') return this;
        fieldIds._type==='field' ? this.orderByStr.push(' ORDER BY ' +Object.keys(this.removePrivate(fieldIds,['_type'])).map(field => `${field} ${typeof fieldIds[field] === 'string'? fieldIds[field].toUpperCase() : 'ASC'}`).join(',')) :
            this.orderByStr.push(' ORDER BY ' + Object.keys(fieldIds).map(tableName => {
                return Object.keys(fieldIds[tableName]).map(field => `${tableName}.${field} ${fieldIds[tableName] ? fieldIds[tableName][field].toUpperCase() : 'ASC'}`).join(',')
            }).join(','))
        return this;
    }

    //分组
    groupBy(fieldIds = {}) {
        if (JSON.stringify(fieldIds) === '{}') throw('need at least a field')
        Array.isArray(fieldIds) ? this.groupByStr.push(' GROUP BY ' + fieldIds.join(' , ')) : this.groupByStr.push(' GROUP BY ' + Object.keys(fieldIds).map(tableName => fieldIds[tableName].map(fieldId => `${tableName}.${fieldId}`).join(' , ')))
        return this;
    }

    //分组条件
    having(conditions) {
        if (JSON.stringify(conditions) === '{}') return this;
        Array.isArray(conditions) ? this.havingStr.push(' HAVING ' + conditions.map(condition => {
            let type = condition._type || ' AND ', nextType = condition._nextType || ' AND ';
            return Object.keys(this.removePrivate(condition, ['_type', '_nextType'])).map(option => this.genCompare(condition[option], false, option)).join(type) + nextType;
        }).join('').slice(0, -5)) : this.havingStr.push(' HAVING ' + Object.keys(conditions).map(table => {
            return conditions[table].map(condition => {
                let type = condition._type || ' AND ', nextType = condition._nextType || ' AND ';
                return Object.keys(this.removePrivate(condition, ['_type', '_nextType'])).map(option => this.genCompare(condition[option], table, option)).join(type) + nextType;
            }).join('').slice(0, -5)
        }))
        return this;
    }

    //限制输出数量和起始位置
    limit(start = 0, length = 1) {
        this.sqlParams = this.sqlParams.concat(start, length)
        this.limitStr.push(` LIMIT ?,?`)
        return this;
    }

    //限制输出起始位置
    skip(nums = 0) {
        this.sqlParams = this.sqlParams.concat(nums)
        this.skipStr.push(` OFFSET ?`)
        return this
    }

    //重置sql
    clean() {
        //common
        this.sql = [];
        this.level = -1;
        this.fieldStr = [];
        this.tableStr = [];
        this.type = [];
        this.whereStr = [];
        this.orderByStr = [];
        this.groupByStr = [];
        this.havingStr = [];
        this.limitStr = [];
        this.skipStr = [];
        //add
        this.insertStr = [];
        //update
        this.updateStr = [];
        this.sqlParams = [];
        this.paramsOprator = []
    }

    link() {
        let sql = ''
        let level = this.level;
        switch (this.type[level]) {
            case "select":
                sql = (this.sql[level] || '') + (this.fieldStr[level] || '') + (this.tableStr[level] || '') + (this.whereStr[level] || '') + (this.groupByStr[level] || '') + (this.havingStr[level] || '') + (this.orderByStr[level] || '') + (this.limitStr[level] || '') + (this.skipStr[level] || '');
                break;
            case 'add':
                sql = (this.sql[level] || '') + (this.insertStr[level] || '');
                break;
            case 'update':
                sql = (this.sql[level] || '') + (this.updateStr[level] || '') + (this.whereStr[level] || '');
                break;
            case 'delete':
                sql = (this.sql[level] || '') + (this.whereStr[level] || '');
                break;
        }
        this.level--;
        return sql;
    }

    //执行sql
    exec() {
        let _this = this;
        let index = 0;
        let params = this.sqlParams[index];
        this.paramsOprator.forEach(item => {
            if (item !== false) {
                params.splice(item, 0, ...this.sqlParams[++index])
            }
        });
        let sql = this.link();
        this.clean();
        return new Promise((resolve, reject) => {
            _this.pool.getConnection(function (err, connection) {
                if (err) {
                    reject({...err, msg: 'MySQL数据库建立连接失败。'})
                } else {
                    connection.query(sql, params, function (err, data) {
                        if (err) {
                            reject({...err, msg: '查询数据操作失败。', sql, params})
                        } else {
                            _this.pool.end();
                            resolve({
                                data: Array.isArray(data) ? data.map(row => {
                                    return {...row}
                                }) : {...data}, sql, params
                            })
                        }
                    });
                }
            })
        });
    }
}
